package com.xl.competition.modul_b.task2

import org.apache.spark.sql.SparkSession;

/**
 * @author: xl
 * @createTime: 2023/11/15 14:44:07
 * @program: com.xl.competition
 * @description:
 */
object LoadSkuInfoToDim {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession
      .builder()
      .master("local[*]")
      .appName(this.getClass.getName)
      .enableHiveSupport()
      .config("hive.metastore.uris", "thrift://node2:9083")
      .config("spark.sql.parquet.writeLegacyFormat", "true")
      .getOrCreate()

    spark.sql(
      """
        |with dsku as (
        |    select id,
        |           spu_id,
        |           price,
        |           sku_name,
        |           sku_desc,
        |           weight,
        |           tm_id,
        |           category3_id,
        |           sku_default_img,
        |           is_sale,
        |           create_time,
        |           dwd_insert_user,
        |           dwd_insert_time,
        |           dwd_modify_user,
        |           dwd_modify_time
        |    from dim.dim_sku_info
        |),
        |     osku as (
        |         select id,
        |                spu_id,
        |                price,
        |                sku_name,
        |                sku_desc,
        |                weight,
        |                tm_id,
        |                category3_id,
        |                sku_default_img,
        |                is_sale,
        |                create_time,
        |                'user1'                                                dwd_insert_user,
        |                'user1'                                                dwd_modify_user,
        |                from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') dwd_insert_time,
        |                from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') dwd_modify_time
        |         from ods.sku_info
        |         where etl_date = '20231116'
        |     )
        |insert into table dim.dim_sku_info partition (etl_date = '20231116')
        |select if(osku.create_time > nvl(dsku.create_time, cast('1970-01-01 00:00:00' as timestamp)), osku.id, dsku.id) as id,
        |       if(osku.create_time > nvl(dsku.create_time, cast('1970-01-01 00:00:00' as timestamp)), osku.spu_id,
        |          dsku.spu_id)                                                                                          as spu_id,
        |       if(osku.create_time > nvl(dsku.create_time, cast('1970-01-01 00:00:00' as timestamp)), osku.price,
        |          dsku.price)                                                                                           as price,
        |       if(osku.create_time > nvl(dsku.create_time, cast('1970-01-01 00:00:00' as timestamp)), osku.sku_name,
        |          dsku.sku_name)                                                                                        as sku_name,
        |       if(osku.create_time > nvl(dsku.create_time, cast('1970-01-01 00:00:00' as timestamp)), osku.sku_desc,
        |          dsku.sku_desc)                                                                                        as sku_desc,
        |       if(osku.create_time > nvl(dsku.create_time, cast('1970-01-01 00:00:00' as timestamp)), osku.weight,
        |          dsku.weight)                                                                                          as weight,
        |       if(osku.create_time > nvl(dsku.create_time, cast('1970-01-01 00:00:00' as timestamp)), osku.tm_id,
        |          dsku.tm_id)                                                                                           as tm_id,
        |       if(osku.create_time > nvl(dsku.create_time, cast('1970-01-01 00:00:00' as timestamp)), osku.category3_id,
        |          dsku.category3_id)                                                                                    as category3_id,
        |       if(osku.create_time > nvl(dsku.create_time, cast('1970-01-01 00:00:00' as timestamp)), osku.sku_default_img,
        |          dsku.sku_default_img)                                                                                 as sku_default_img,
        |       if(osku.create_time > nvl(dsku.create_time, cast('1970-01-01 00:00:00' as timestamp)), osku.is_sale,
        |          dsku.is_sale)                                                                                         as is_sale,
        |       if(osku.create_time > nvl(dsku.create_time, cast('1970-01-01 00:00:00' as timestamp)), osku.create_time,
        |          dsku.create_time)                                                                                     as create_time,
        |       'user1'                                                                                                  as dwd_insert_user,
        |       nvl(dsku.dwd_insert_time, substr(current_timestamp(), 1, 19))                                            as dwd_insert_time,
        |       'user1'                                                                                                  as dwd_modify_user,
        |       substr(current_timestamp(), 1, 19)                                                                       as dwd_modify_time
        |from osku
        |         left join dsku
        |                   on osku.id = dsku.id
        |where unix_timestamp(osku.create_time) > (select max(unix_timestamp(create_time)) from dim.dim_sku_info)
        |""".stripMargin)

    spark.stop()
  }
}
